package com.hy.dao.impl;

import com.hy.bean.Goods;
import com.hy.dao.GoodsDao;
import com.hy.utils.DBManager;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class GoodsDaoImpl implements GoodsDao {
    private QueryRunner qr = new QueryRunner();

    @Override
    public List<Map<String, Object>> selectAll(String name) throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "SELECT g.*,classname,username FROM goods g INNER JOIN CLASSIFY c on g.classify_id = c.id LEFT JOIN CONSUMER co ON\n" +
                "co.id = g.record_user_id where goodsname like ? ORDER BY g.id";
        return qr.query(conn, sql, new MapListHandler(),"%"+name+"%");
    }

    @Override
    public List<Goods> queryByName(){
        Connection connection = null;
        String sql = "SELECT id,goodsname,brand,spec,goods_count goodsCount,total_money totalMoney, record_date recordDate,record_user_id recordUserId,remark,classify_id classifyId FROM goods";
        try {
            connection = DBManager.getConnection();
            return qr.query(connection, sql, new BeanListHandler<>(Goods.class));
        }catch (Exception e){
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public BigDecimal count(int id) throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "select count(*) from goods g INNER JOIN purchase_apply_detail p on g.id = p.goods_id where g.id = ?";
        BigDecimal count = qr.query(conn, sql, new ScalarHandler<BigDecimal>(),id);
        return count;
    }

    @Override
    public int insert(Goods goods) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String dateString = sdf.format(new Date());
        Connection conn = DBManager.getConnection();
        String sql = "INSERT INTO goods values(seq_goods.nextval,?,?,?,?,?,to_date(?,'yyyy-mm-dd'),?,?,?)";
        return qr.update(conn, sql,goods.getGoodsname(),goods.getBrand()
                ,goods.getSpec(),goods.getGoodsCount(),goods.getTotalMoney(),dateString,
                goods.getRecordUserId(),goods.getRemark(),goods.getClassifyId());
    }

    @Override
    public int insertBatch(List<Goods> entities) {
        return 0;
    }

    @Override
    public int insertOrUpdateBatch(List<Goods> entities) {
        return 0;
    }

    @Override
    public int update(Goods goods) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String dateString = sdf.format(new Date());
        Connection conn = DBManager.getConnection();
        String sql = "update goods set goodsname=?,brand=?,spec=?,goods_count=?,total_money=?,record_date=to_date(?, 'yyyy-mm-dd'),record_user_id=?,remark=?,classify_id=? where id=?";
        return qr.update(conn, sql,goods.getGoodsname(),goods.getBrand()
                ,goods.getSpec(),goods.getGoodsCount(),goods.getTotalMoney(),dateString,
                goods.getRecordUserId(),goods.getRemark(),goods.getClassifyId(),goods.getId());
    }

    @Override
    public int deleteById(int id) throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "delete from goods where id=?";
        return qr.update(conn, sql,id);
    }

}
